package com.glureau.aib.server.db.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.glureau.aib.shared.ai.AiInfo;

public class DB_Ai extends DB_Conn {

	/**
	 * constructor
	 */
	public DB_Ai() {
	}

	public void addAi(int index, String name, String jar, String description,
			String parameters, int userId) {
		// SQL syntax used for a DATETIME : 'YYYY-MM-DD HH:MM:SS'
		Calendar c = Calendar.getInstance();
		String sqlTimestamp = c.get(Calendar.YEAR) + "-"
				+ (c.get(Calendar.MONTH) + 1) + "-"
				+ c.get(Calendar.DAY_OF_MONTH) + " "
				+ c.get(Calendar.HOUR_OF_DAY) + ":" + c.get(Calendar.MINUTE)
				+ ":" + c.get(Calendar.SECOND);
		String query = "INSERT INTO ai (name, jar, description, param, userId, numero, date) VALUES('"
				+ name
				+ "', '"
				+ jar
				+ "', '"
				+ description
				+ "', '"
				+ parameters
				+ "', '"
				+ userId
				+ "', '"
				+ index 
				+ "', '"
				+ sqlTimestamp + "');";
		System.out.println(query);
		try {
			Connection connection = this.getConn();
			Statement insert = connection.createStatement();
			insert.executeUpdate(query);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<AiInfo> getAis(String sessionId) {
		try {
			ArrayList<AiInfo> infos = new ArrayList<AiInfo>();

			Connection connection = this.getConn();
			Statement select = connection.createStatement();

			String query = "SELECT name, jar, description, param, numero, MAX(ai.date) FROM ai INNER JOIN connection ON ai.userId=connection.userId WHERE connection.sessionId='"
					+ sessionId + "' GROUP BY numero;";
			System.out.println(query);
			ResultSet result = select.executeQuery(query);
			while (result.next()) {
				AiInfo aiInfo = new AiInfo();
				aiInfo.setName(result.getString(1));
				aiInfo.setAiFileHashcode(result.getString(2));
				aiInfo.setDescription(result.getString(3));
				aiInfo.setParams(result.getString(4));
				aiInfo.setNumero(result.getInt(5));
				infos.add(aiInfo);
			}
			connection.close();
			return infos;
		} catch (Exception e) {
			e.printStackTrace();
			System.exit(0);
		}
		return null;
	}

}
